package dao;

import bean.novelBean;
import bean.userBean;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class novelDao {

    Db db = new Db();											//新建实例（该实例实现了数据的增删改查）
    String sql = "";
    ResultSet rs = null;

    /**------------------------------ 获取记录总数 */
    public int selectCount (HttpServletRequest request, String search) {

        int count = 0;
        String msg = "";

        try {															//使用try-catch，便于传递消息
            String sqlWhere = "";

            if (search.equals("") == false) {							//如果有搜索内容
                //sqlWhere = " where novelName like '%陈%'";				//增加模糊查询
                sqlWhere = " where concat_ws(',', novelNumber, novelName, typeName) like ?";	//多字段模糊查询
            }

            if (search.equals("")) {
                sql = "select count(*) from tb_novel"; 					//查询到记录总数
                rs = db.select(sql); 									//执行查询，得到结果集
            } else {
                sql = "select count(*) from tb_novel " + sqlWhere; 		//模糊查询
                rs = db.select(sql, "%" + search + "%");
            }

            if (rs == null) {
                msg += "数据库操作发生错误！";
                return count;
            }

            if (rs.next() == false) {
                db.close();
                return count;
            }
            count = rs.getInt(1);
            db.close();

        } catch (Exception e) {
            msg += "系统发生错误。";
            e.printStackTrace();							//输出异常信息到控制台
        } finally {
            if (request.getAttribute("msg") != null) {
                msg += request.getAttribute("msg").toString();
            }
            request.setAttribute("msg", msg);
        }

        return count;										//满足条件的记录数可能为0
    }

    /**------------------------------ 获取满足查询条件的要显示的页中的记录列表 */
    public List<novelBean> selectAll (HttpServletRequest request, String search, int pageShow, int pageSize) {

        List<novelBean> novelList = new ArrayList<>();					//
        String msg = "";

        try {
            String novelNumber,novelName,novelImage, novelIntroduce;

            int countShowed = 0;

            if (pageShow > 0) {
                countShowed = (pageShow - 1) * pageSize;				//（要略过的）之前的记录数
            }

            String sqlWhere = "";

            if (search.equals("") == false) {							//如果有搜索内容
                sqlWhere = " where concat_ws(',', novelNumber, novelName, typeName) like ?";	//多字段模糊查询
            }

            if (search.equals("")) {
                sql = "select * from tb_novel order by novelName"
                        + " limit " + countShowed + "," + pageSize; 	//只获取该页的记录
                rs = db.select(sql);
            } else {
                sql = "select * from tb_novel " + sqlWhere + " order by novelName"
                        + " limit " + countShowed + "," + pageSize;
                rs = db.select(sql, "%" + search + "%"); 				//模糊查询
            }

            if (rs == null) {
                msg += "数据库操作发生错误！";
                return novelList;
            }

            novelBean novel = null;

            while (rs.next()) {
                novelNumber 	= rs.getString("novelNumber");
                novelName 	= rs.getString("novelName");
                novelImage 		= rs.getString("novelImage");
                novelIntroduce 		= rs.getString("novelIntroduce");

                novel = new novelBean();
                novel.setNovelNumber(novelNumber);
                novel.setNovelName(novelName);
                novel.setNovelImage(novelImage);
                novel.setNovelIntroduce(novelIntroduce);

                novelList.add(novel);

            }
            db.close();

        } catch (Exception e) {
            msg += "系统发生错误。";
            e.printStackTrace();
        } finally {
            if (request.getAttribute("msg") != null) {
                msg += request.getAttribute("msg").toString();
            }
            request.setAttribute("msg", msg);
        }

        return novelList;			//返回对象列表
    }

    public novelBean selectByName( String novelName) throws Exception {
        String sql = "select * from db_novel.tb_novel where novelName=?";
        ResultSet rs = db.select(sql, novelName);
        if (rs == null) {
           throw  new Exception("数据库操作发生错误！");
        }

        if (rs.next() == false) {

            db.close();
            throw  new Exception("数据库查询结果为空！");
        }
        novelBean novel =new novelBean();
        novel.setNovelName(rs.getString("novelName"));
        novel.setNovelNumber(rs.getString("novelNumber"));
        novel.setNovelIntroduce(rs.getString("novelIntroduce"));
        novel.setNovelImage(rs.getString("novelImage"));
        return novel;
    }
    /**------------------------------ 新添记录 */
    public String insert (HttpServletRequest request, novelBean novel) {

        String novelNumber = "0";
        String msg = "";

        try {
            String  novelName,novelIntroduce;

            novelName	= novel.getNovelName();
            novelIntroduce 		= novel.getNovelIntroduce();

            sql = "insert tb_novel (novelName, novelIntroduce)"
                    + " values (?, ?)";
            novelNumber = db.insert(sql, novelName,  novelIntroduce);

            if (novelNumber.equals("0")) {
                //msg += "商品新添失败！";
                return novelNumber;
            }

            //msg = "商品新添成功！";		
            return novelNumber;

        } catch (Exception e) {
            msg += "系统发生错误。";
            e.printStackTrace();
        } finally {
            if (request.getAttribute("msg") != null) {
                msg += request.getAttribute("msg").toString();
            }
            request.setAttribute("msg", msg);
        }

        return novelNumber;
    }

    /**------------------------------ 更新记录 */
    public int update (HttpServletRequest request, novelBean novel) {

        int count = 0;
        String msg = "";

        try {
            String novelNumber, novelName,  novelIntroduce;

            novelNumber 	= novel.getNovelNumber();
            novelName	= novel.getNovelName();
            novelIntroduce 		= novel.getNovelIntroduce();

            sql = "update tb_novel set  novelName=?, novelIntroduce=? where novelNumber=?";
            count = db.update(sql,  novelName,  novelIntroduce, novelNumber);

            if (count == 0) {
                //msg = "商品信息修改失败！";
                return count;
            }

            //msg = "商品信息修改成功！";		
            return count;

        } catch (Exception e) {
            msg += "系统发生错误。";
            e.printStackTrace();
        } finally {
            if (request.getAttribute("msg") != null) {
                msg += request.getAttribute("msg").toString();
            }
            request.setAttribute("msg", msg);
        }

        return count;
    }

    /**------------------------------ 更新记录：更新商品图片image */
    public int update (HttpServletRequest request, String novelNumber, String novelImage) {

        int count = 0;
        String msg = "";

        try {

            sql = "update tb_novel set novelImage=? where novelNumber=?";
            count = db.update(sql, novelImage, novelNumber);

            if (count == 0) {
                //msg = "商品信息修改失败！";
                return count;
            }

            //msg = "商品信息修改成功！";
            return count;

        } catch (Exception e) {
            msg += "系统发生错误。";
            e.printStackTrace();
        } finally {
            if (request.getAttribute("msg") != null) {
                msg += request.getAttribute("msg").toString();
            }
            request.setAttribute("msg", msg);
        }

        return count;
    }

    public boolean deleteById (HttpServletRequest request, String novelNumber) {

        String msg = "";

        try {
            sql = "delete from tb_novel where novelNumber=?";
            int count = db.delete(sql, novelNumber);

            if (count == 0) {
                //msg += "商品删除失败！";
                return false;
            }

            //msg = "商品删除成功！";
            return true;

        } catch (Exception e) {
            msg += "系统发生错误。";
            e.printStackTrace();
        } finally {
            if (request.getAttribute("msg") != null) {
                msg += request.getAttribute("msg").toString();
            }
            request.setAttribute("msg", msg);
        }

        return false;
    }

    /**------------------------------ 删除ID列表中的记录 */
    public boolean deleteByIdList (HttpServletRequest request, String novelNumberAll) {

        String msg = "";

        try {
            sql = "delete from tb_novel where novelNumber in (" + novelNumberAll + ")";		//批量删除记录
            int count = db.delete(sql);

            if (count == 0) {
                //msg += "商品批量删除失败！";
                return false;
            }

            //msg = "商品批量删除成功！";
            return true;

        } catch (Exception e) {
            msg += "系统发生错误。";
            e.printStackTrace();
        } finally {
            if (request.getAttribute("msg") != null) {
                msg += request.getAttribute("msg").toString();
            }
            request.setAttribute("msg", msg);
        }

        return false;
    }

    /**------------------------------ 根据ID查询记录 */
    public novelBean selectById (HttpServletRequest request, String novelNumber) {

        novelBean novel = null;
        String msg = "";

        try {
            String novelName, novelImage, novelIntroduce;

            sql = "select * from tb_novel where novelNumber=?";
            rs = db.select(sql, novelNumber);

            if (rs == null) {
                msg += "数据库操作发生错误！";
                return novel;
            }

            if (rs.next() == false) {
                db.close();
                //msg += "goodsId号对应的记录已不存在，请刷新页面后重试！";
                return novel;
            }

            novelName 	= rs.getString("novelName");
            novelImage 		= rs.getString("novelImage");
            novelIntroduce 		= rs.getString("novelIntroduce");
            db.close();

            novel = new novelBean();						//创建对象

            novel.setNovelNumber(novelNumber);
            novel.setNovelName(novelName);
            novel.setNovelImage(novelImage);
            novel.setNovelIntroduce(novelIntroduce);

        } catch (Exception e) {
            msg += "系统发生错误。";
            e.printStackTrace();
        } finally {
            if (request.getAttribute("msg") != null) {
                msg += request.getAttribute("msg").toString();
            }
            request.setAttribute("msg", msg);
        }

        return novel;
    }
}
